﻿using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.ExcelModel;
using Estsh.Core.Model.Extend.EnumUtil;
using Estsh.Core.Model.Extend.IFModels;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using Microsoft.Extensions.Primitives;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Ocsp;
using SqlSugar.DistributedSystem.Snowflake;
using System;
//using Estsh.Core.Repository.IRepositories;
using System.Collections;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：处理菜单模块的业务数据
 * 修改时间:20223.03.09
 * 描述：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Wms.Repositories
{

    public class WorkOrderRepository : BaseRepository<MesWorkOrder_Extend>, IWorkOrderRepository
    {

        public WorkOrderRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        #region 成员方法

        /// <summary>
        /// 产线
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetResourceCode(int factoryId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " SELECT [pdline_code] as [value],[pdline_name] as [key] FROM sys_pdline (nolock)  where  factory_id='" + factoryId + "' and enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 工单类型
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetOrderType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " SELECT [enum_value] as [value],[enum_name] as [key] FROM sys_enum (nolock)  where enum_type='MES_WMS_WORK_ORDER_ORDER_TYPE' and enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 工单状态
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetOrderStatus()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum (nolock)  where enum_type='mes_work_order_status' and enabled='Y' ORDER BY enum_seq  ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 备料类型
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetPrepareMateria()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum (nolock)  where enum_type='wms_Move_Header_Prepare_Material' and enabled='Y' ORDER BY enum_seq   ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 仓库
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetWarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = " SELECT [warehouse_id] as [value],[warehouse_desc] as [key] FROM dbo.sys_warehouse (nolock)  where   enabled='Y' ORDER BY warehouse_id  ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表-主数据显示
        /// </summary>
        public Hashtable getPurchaseListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", " dbo.mes_work_order a LEFT JOIN sys_enum b ON b.enum_type='mes_work_order_status' AND a.order_status=b.enum_value LEFT JOIN sys_enum c ON c.enum_type='MES_WMS_WORK_ORDER_ORDER_TYPE' AND a.order_type=c.enum_value  ");//查询sql的主体
                Params.Add("@Column", " a.ruid,a.order_no, a.work_code,a.part_no,a.pdline_name,a.ref_work_order_no,a.equipment_code,a.qty,a.classes_name, a.produce_start_time,a.produce_end_time,b.enum_name AS order_status_name,c.enum_name AS order_type_name ");//显示的字段
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);//条件
                Params.Add("@OrderColumn", " a.produce_time,a.order_status desc ");
                Params.Add("@Group", 0);

                List<MesWorkOrder_Extend> dataList = dbConn.Query<MesWorkOrder_Extend>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();

                foreach (var item in dataList)
                {
                    if (item.ProduceStartTime.ToString().Length > 0)
                    {
                        string time= item.ProduceStartTime.ToString();
                        item.strProduceStartTime = time;
                    }
                    if (item.ProduceEndTime.ToString().Length > 0)
                    {
                        string time = item.ProduceEndTime.ToString();
                        item.strProduceEndTime = time;
                    }

                }


                result.Add("dataList", dataList);//查询的结果
                result.Add("totalCount", Params.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 生产备料
        /// </summary>
        /// <param name="prepareMaterial">备料方法</param>
        /// <param name="orderNos">工单ids</param>
        /// <param name="Warehouse">仓库</param>
        /// <param name="empId">修改人id</param>
        /// <returns></returns>
        public int saveGenerateIngredients(string prepareMaterial, string orderNos, string Warehouse, int empId)
        {

            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                var deviceVarID = orderNos;
                string[] strArray = deviceVarID.Split(',');

                #region 查询工单数据

                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT * FROM dbo.mes_work_order (NOLOCK) WHERE  order_status IN (10,20)  AND order_no IN(" + orderNos + ") ");
                List<MesWorkOrder_Extend> mesWorkOrderInfos = dbConn.Query<MesWorkOrder_Extend>(strSql.ToString()).ToList();
                if (mesWorkOrderInfos.Count == 0)
                {
                    return 0;
                }

                foreach (var item in mesWorkOrderInfos)
                {
                    if (item.OrderType == (int)EnumUtil_Extend.MesWorkOrderType.REWORK)//工单为返工类型的，不让备料！
                    {
                        return -2;//提示有返修工单，请查看！;
                    }

                }


                #endregion

                #region 查询移动单数据

                StringBuilder strSqlMoveHeader = new StringBuilder();
                strSqlMoveHeader.Append("SELECT  * FROM dbo.wms_move_header(NOLOCK)  WHERE enabled='Y'   and  order_status=10   AND ref_order_no IN (" + orderNos + ")");//在移动单中有生成过的。，在处理中的数据，不可以重新备料
                List<WmsMoveHeader_Extend> wmsMoveHeaderinfos = dbConn.Query<WmsMoveHeader_Extend>(strSqlMoveHeader.ToString()).ToList();

                #endregion

                List<string> sqlStrings = new List<string>();
                StringBuilder stringBuilder = new StringBuilder(1024);
                List<DynamicParameters> parameterList = new List<DynamicParameters>();
                if (wmsMoveHeaderinfos.Count != 0 && wmsMoveHeaderinfos.Count != strArray.Count())
                {
                    return -1;//表示：当前选择的工单有生成备料的，但是有的数量和选择工单的数量不相符
                }
                else if (wmsMoveHeaderinfos.Count != 0) {

                    stringBuilder = new StringBuilder();
                    stringBuilder.Append(" DELETE FROM dbo.mes_work_order_part  WHERE order_no IN (" + orderNos + "); ");//删除工单明细表
                    foreach (var moves in wmsMoveHeaderinfos)
                    {
                       
                        stringBuilder.Append(" DELETE FROM dbo.wms_move_header WHERE order_no='" + moves.OrderNo + "'; ");//删除移动单主表
                        stringBuilder.Append(" DELETE FROM dbo.wms_move_detail WHERE order_no='" + moves.OrderNo + "'; ");//删除移动单明细表
                        sqlStrings.Add(stringBuilder.ToString());
                    }
                   
                    

                }//修改

                #region 生成单据

                foreach (var mesWorkOrderInfo in mesWorkOrderInfos)
                {
                    //生成移动单号
                    string GDOrderNo = GetOrderNo("GDList", "GD");
                    GDOrderNo = GDOrderNo.Trim().ToString();//去掉空格

                    //添加move_header主表
                    #region 添加移动单主表 

                    stringBuilder = new StringBuilder();
                    stringBuilder.Append("INSERT INTO dbo.wms_move_header ");
                    stringBuilder.Append("( ");
                    stringBuilder.Append("    order_no, ");
                    stringBuilder.Append("    order_type, ");
                    stringBuilder.Append("    order_status, ");
                    stringBuilder.Append("    ref_order_no, ");
                    stringBuilder.Append("    factory_id, ");
                    stringBuilder.Append("    factory_code, ");
                    stringBuilder.Append("    batching_status, ");
                    stringBuilder.Append("    wrong_sign, ");
                    stringBuilder.Append("    prepare_material, ");
                    stringBuilder.Append("    warehouse_id, ");
                    stringBuilder.Append("    enabled, ");
                    stringBuilder.Append("    remarks, ");
                    stringBuilder.Append("    create_userid, ");
                    stringBuilder.Append("    create_time, ");
                    stringBuilder.Append("    guid ");
                    stringBuilder.Append(") ");
                    stringBuilder.Append("VALUES ");
                    stringBuilder.Append("(   '" + GDOrderNo + "', ");//移动单号
                    stringBuilder.Append("   '" + (int)WmsEnumUtil.MoveOrderType.PICK + "', ");//类型  生产领料单
                    stringBuilder.Append("   '" + (int)WmsEnumUtil.MoveOrderStatus.CREATE + "', ");//状态
                    stringBuilder.Append("   '" + mesWorkOrderInfo.OrderNo + "', ");//工单号
                    stringBuilder.Append("   '" + mesWorkOrderInfo.FactoryId + "', ");//工厂id
                    stringBuilder.Append("   '" + mesWorkOrderInfo.FactoryCode + "', ");//工厂代码
                    stringBuilder.Append("   '0', ");//批量不确定？
                    stringBuilder.Append("   '" + null + "', ");
                    stringBuilder.Append("   '" + prepareMaterial + "', ");//备料类型 
                    stringBuilder.Append("   '" + Warehouse + "', ");//仓库id
                    stringBuilder.Append("   'Y', ");
                    stringBuilder.Append("    '" + null + "', ");
                    stringBuilder.Append("    '" + empId + "', ");
                    stringBuilder.Append("    CONVERT(VARCHAR(20),GETDATE(),21), ");
                    stringBuilder.Append("    NEWID()  ");
                    stringBuilder.Append("    ) ; ");
                    sqlStrings.Add(stringBuilder.ToString());

                    #endregion


                    #region 查询bom 表

                    StringBuilder SqlStringBuildeBomr = new StringBuilder(1024);
                    SqlStringBuildeBomr.Append(" SELECT bd.* FROM dbo.sys_bom (NOLOCK) b  LEFT JOIN dbo.sys_bom_detail bd ON b.bom_id=bd.bom_id  WHERE b.enabled='Y' AND b.part_id='" + mesWorkOrderInfo.PartId + "'  ");//BOM明细表
                    List<SysBomDetail> sysBomDetails = dbConn.Query<SysBomDetail>(SqlStringBuildeBomr.ToString()).ToList();

                    #endregion

                    #region 查询产线表

                    StringBuilder SqlStringBuildepdline = new StringBuilder(1024);
                    SqlStringBuildepdline.Append(" SELECT * FROM dbo.sys_pdline (NOLOCK) WHERE enabled='Y' AND pdline_id='" + mesWorkOrderInfo.PdlineId + "' ; ");//通过工单的产线，找到它的目的库区信息
                    List<SysPdline> sysPdlines = dbConn.Query<SysPdline>(SqlStringBuildepdline.ToString()).ToList();

                    #endregion


                    if (sysBomDetails.Count > 0)
                    {
                        for (int i = 0; i < sysBomDetails.Count; i++)//循环bom
                        {
                            #region 查询库位推荐表sys_zone_part

                            StringBuilder SqlStringBuildeZone = new StringBuilder(1024);
                            SqlStringBuildeZone.Append(" SELECT z.warehouse_id,z.warehouse_name,z.erp_warehouse,  zp.* FROM dbo.sys_zone_part zp (NOLOCK) LEFT JOIN dbo.sys_zone z ON zp.zone_id=z.zone_id  WHERE zp.enabled='Y' AND zp.part_id='" + sysBomDetails[i].ItemPartId + "' order by zp.priority  ;");//找到零件号的来源仓库、库区 有优先级关系
                            List<SysZonePart_Extend> zoneParts = dbConn.Query<SysZonePart_Extend>(SqlStringBuildeZone.ToString()).ToList();

                            #endregion

                          

                            #region 查父阶零件表信息

                            StringBuilder SqlStringBuilPart = new StringBuilder(1024);
                            SqlStringBuilPart.Append(" SELECT * FROM dbo.sys_part(NOLOCK) WHERE enabled='Y' AND part_id='" + mesWorkOrderInfo.PartId + "'  ");//找父阶零件表信息
                            List<SysPart> sysPart = dbConn.Query<SysPart>(SqlStringBuilPart.ToString()).ToList();

                            #endregion

                            #region 子阶零件号信息查询零件表
                            stringBuilder = new StringBuilder();
                            stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + sysBomDetails[i].ItemPartNo + "';");//子阶零件号信息
                            List<SysPart> itemSysParts = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                            #endregion


                            #region 添加工单明细 workOrderPart

                            stringBuilder = new StringBuilder();
                            stringBuilder.Append("INSERT INTO dbo.mes_work_order_part(order_no,part_id,part_no,part_spec,unit,qty,pull_status ");
                            stringBuilder.Append("           ,parent_part_id,parent_part_no,parent_part_spec,factory_id, factory_code, enabled ");
                            stringBuilder.Append("           ,create_userid,create_time) ");
                            stringBuilder.Append("VALUES (  ");

                            stringBuilder.Append(" '" + mesWorkOrderInfo.OrderNo + "', ");

                            if (itemSysParts.Count > 0)
                            {
                                //子零件号ID  //子简码  //子单位
                                stringBuilder.Append(" " + itemSysParts[0].PartId + ", "); stringBuilder.Append(" '" + itemSysParts[0].PartNo + "', "); stringBuilder.Append(" '" + itemSysParts[0].PartSpec + "', ");
                                stringBuilder.Append(" '" + itemSysParts[0].Unit + "', ");
                            }
                            else
                            {
                                stringBuilder.Append(" " + 0 + ", "); stringBuilder.Append(" '" + null + "', "); stringBuilder.Append(" '" + null + "', ");
                                stringBuilder.Append(" '" + null + "', ");
                            }

                            stringBuilder.Append(" '" + ((mesWorkOrderInfo.Qty * sysBomDetails[i].Qty) ==null?0: mesWorkOrderInfo.Qty * sysBomDetails[i].Qty) + "', ");//成品个数*零件号数量
                            stringBuilder.Append(" '" + 10 + "', ");


                            if (sysPart.Count > 0)
                            {
                                ////父零件号ID  //父零件号  //父简码
                                stringBuilder.Append(" '" + sysPart[0].PartId + "', "); stringBuilder.Append(" '" + sysPart[0].PartNo + "', "); stringBuilder.Append(" '" + sysPart[0].PartSpec + "', ");

                            }
                            else
                            {
                                stringBuilder.Append(" '" + 0 + "', "); stringBuilder.Append(" '" + null + "', "); stringBuilder.Append(" '" + null + "', ");
                            }

                            stringBuilder.Append(" '" + mesWorkOrderInfo.FactoryId + "', "); stringBuilder.Append(" '" + mesWorkOrderInfo.FactoryCode + "', "); stringBuilder.Append("  'Y', ");
                            stringBuilder.Append("  '" + empId + "', "); stringBuilder.Append("   '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + " ' ");
                            stringBuilder.Append("    ) ; ");


                            sqlStrings.Add(stringBuilder.ToString());

                            #endregion


                            #region 添加 移动单明细

                            stringBuilder = new StringBuilder();
                            stringBuilder.Append("INSERT INTO dbo.wms_move_detail ");
                            stringBuilder.Append("( ");
                            stringBuilder.Append("    order_no, "); stringBuilder.Append("    item_no, "); 
                            stringBuilder.Append("    part_id, "); stringBuilder.Append("    part_no, "); stringBuilder.Append("    part_spec, ");
                            stringBuilder.Append("    qty, ");  stringBuilder.Append("    pick_qty, "); stringBuilder.Append("    out_qty, "); stringBuilder.Append("    rec_qty, "); 
                            stringBuilder.Append("    unit, ");
                            stringBuilder.Append("    plan_date, "); stringBuilder.Append("    plan_time, "); stringBuilder.Append("    item_status, ");

                            stringBuilder.Append("    src_warehouse_id, "); stringBuilder.Append("    src_warehouse_name, "); stringBuilder.Append("    src_zone_id, "); stringBuilder.Append("    src_zone_name, ");
                            stringBuilder.Append("    src_erp_warehouse, "); //stringBuilder.Append("    src_locate_id, "); stringBuilder.Append("    src_locate_name, ");

                            stringBuilder.Append("    dest_warehouse_id, "); stringBuilder.Append("    dest_warehouse_name, "); stringBuilder.Append("    dest_zone_id, "); stringBuilder.Append("    dest_zone_name, ");
                            // stringBuilder.Append("    dest_erp_warehouse, "); 
                            stringBuilder.Append("    dest_locate_id, "); stringBuilder.Append("    dest_locate_name, ");

                            stringBuilder.Append("    factory_id, "); stringBuilder.Append("    factory_code, "); stringBuilder.Append("    enabled, "); stringBuilder.Append("    create_userid, ");
                            stringBuilder.Append("    create_time, "); stringBuilder.Append("    guid, "); stringBuilder.Append("    is_manual_pull ");  stringBuilder.Append(") ");
                            stringBuilder.Append("VALUES (  ");

                            stringBuilder.Append(" '" + GDOrderNo + "', "); stringBuilder.Append("   '" + (i + 1) * 10 + "', "); //移动单号、行号、
                            //stringBuilder.Append("   '" + sysBomDetails[i].PartId + "', "); stringBuilder.Append("   '" + sysBomDetails[i].PartNo + "', "); stringBuilder.Append("   '" + (sysPart.Count > 0 ? sysPart[0].PartSpec : null) + "', ");//零件号id、零件号名称、零件简码
                            //stringBuilder.Append("   '" + itemSysParts[0].PartId + "', "); stringBuilder.Append("   '" + itemSysParts[0].PartNo + "', "); stringBuilder.Append("   '" + (itemSysParts.Count > 0 ? itemSysParts[0].PartSpec : null) + "', ");//零件号id、零件号名称、零件简码
                            if (itemSysParts.Count > 0)
                            {
                                //子零件号ID  //子简码  //子单位
                                stringBuilder.Append(" " + itemSysParts[0].PartId + ", "); stringBuilder.Append(" '" + itemSysParts[0].PartNo + "', "); stringBuilder.Append("   '" + (itemSysParts.Count > 0 ? itemSysParts[0].PartSpec : null) + "', ");//零件号id、零件号名称、零件简码
                            }
                            else
                            {
                                stringBuilder.Append(" " + 0 + ", "); stringBuilder.Append(" '" + null + "', "); stringBuilder.Append(" '" + null + "', ");
                            }



                            stringBuilder.Append(" " + ((mesWorkOrderInfo.Qty * sysBomDetails[i].Qty)!=null? (mesWorkOrderInfo.Qty * sysBomDetails[i].Qty):0) + ", "); //成品个数*零件号数量 需求数量
                            stringBuilder.Append("   " +0 + ", "); stringBuilder.Append("   " + 0 + ", "); stringBuilder.Append(" " + 0 + ", "); stringBuilder.Append("   '" + sysBomDetails[i].Unit + "', ");//配料数量、出库数量、接收数量、接收数量 待定
                           

                            stringBuilder.Append(" '" + mesWorkOrderInfo.ProduceStartTime.ToString().Substring(0,9) + "', "); stringBuilder.Append("   '" + mesWorkOrderInfo.ProduceStartTime.ToString().Substring(9, 8) + "', "); stringBuilder.Append("   '" + 10 + "', ");//开始日期、开始时间、行状态

                            if (zoneParts.Count > 0)//来源
                            {

                                stringBuilder.Append("  " + zoneParts[0].warehouseId + ", "); stringBuilder.Append(" '" + zoneParts[0].warehouseName + "', "); stringBuilder.Append(" '" + zoneParts[0].ZoneId + "', "); stringBuilder.Append(" '" + zoneParts[0].ZoneName + "', ");
                                stringBuilder.Append("   '" + zoneParts[0].erpWarehouse + "', ");// stringBuilder.Append("    src_locate_id, "); stringBuilder.Append("    src_locate_name, ");
                            }
                            else
                            {
                                stringBuilder.Append("  " + 0 + ", "); stringBuilder.Append(" '" + null + "', "); stringBuilder.Append(" '" + 0 + "', "); stringBuilder.Append(" '" + null + "', ");
                                stringBuilder.Append("   '" + null + "', ");
                            }
                            //目的仓库、库区、库位
                            if (sysPdlines.Count > 0)
                            {
                                stringBuilder.Append(" '" + sysPdlines[0].SrcWarehouseId + "', "); stringBuilder.Append(" ' " + sysPdlines[0].SrcWarehouseName + "', "); stringBuilder.Append(" '" + sysPdlines[0].SrcZoneId + "', "); stringBuilder.Append(" '" + sysPdlines[0].SrcZoneName + "', ");
                                //stringBuilder.Append(" dest_erp_warehouse, ");
                                stringBuilder.Append("  '" + sysPdlines[0].SrcLocateId + "', "); stringBuilder.Append(" '" + sysPdlines[0].SrcLocateName + "', ");
                            }
                            else
                            {
                                stringBuilder.Append(" '" + 0 + "', "); stringBuilder.Append(" ' " + null + "', "); stringBuilder.Append(" '" + 0 + "', "); stringBuilder.Append(" '" + null + "', ");
                                //stringBuilder.Append(" dest_erp_warehouse, ");
                                stringBuilder.Append("  '" + 0 + "', "); stringBuilder.Append(" '" + null + "', ");
                            }
                            stringBuilder.Append("   '" + mesWorkOrderInfo.FactoryId + "', "); stringBuilder.Append("   '" + mesWorkOrderInfo.FactoryCode + "', "); stringBuilder.Append("   'Y', "); stringBuilder.Append("    '" + empId + "', ");
                            stringBuilder.Append("    CONVERT(VARCHAR(20),GETDATE(),21), "); stringBuilder.Append("    NEWID(),  "); stringBuilder.Append(" '"+ (zoneParts[0].IsManualPull==null?0 : zoneParts[0].IsManualPull)+"'"); stringBuilder.Append("    ) ; ");


                            sqlStrings.Add(stringBuilder.ToString());



                            #endregion
                        }


                        stringBuilder = new StringBuilder();
                        stringBuilder.Append(" UPDATE  dbo.mes_work_order SET order_status=20, update_userid='" + empId + "',update_time=' CONVERT(VARCHAR(20),GETDATE(),21)'  WHERE order_no IN (" + orderNos + "); ");//删除工单明细表
                        sqlStrings.Add(stringBuilder.ToString());

                    }
                    else
                    {
                        return 0;
                    }

                 


                }

                #endregion


                int refNum = SQLTransaction(sqlStrings, parameterList);
                if (refNum > 0)
                {
                    return 1;
                }
                else
                {
                   return 0;
                }



            }

        }

        /// <summary>
        /// 生成订单编号
        /// </summary>
        /// <returns></returns>
        private string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);// this._remotingProxy.ExecuteSotreProcedure("dbo.sys_create_orderno", list);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }


        /// <summary>
        /// 启动
        /// </summary>
        /// <param name="orderNos"></param>
        /// <returns></returns>
        public int FiringData(String orderNos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = " UPDATE dbo.mes_work_order SET order_status=30 WHERE (order_status=40 or ( order_status in (10,40) AND  order_type= "+(int)EnumUtil_Extend.MesWorkOrderType.REWORK+")) AND  order_no in (" + orderNos + ");";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        /// <summary>
        /// 暂停
        /// </summary>
        /// <param name="orderNos"></param>
        /// <returns></returns>
        public int SuspendData(String orderNos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = " UPDATE dbo.mes_work_order SET order_status=40 WHERE order_status=30 AND  order_no in (" + orderNos + ") ;";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        ///取消、关闭、完成   在状态 为下达之后的状态都可以修改
        /// <summary>
        /// 取消
        /// </summary>
        /// <param name="orderNos"></param>
        /// <returns></returns>
        public int CancelData(String orderNos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = " UPDATE dbo.mes_work_order SET order_status=70 WHERE order_status>20 AND  order_no in (" + orderNos + ") ;";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        /// <summary>
        ///  关闭
        /// </summary>
        /// <param name="orderNos"></param>
        /// <returns></returns>
        public int CloseData(String orderNos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = " UPDATE dbo.mes_work_order SET order_status=60 WHERE order_status>20 AND  order_no in (" + orderNos + ") ;";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        /// <summary>
        ///  完成
        /// </summary>
        /// <param name="orderNos"></param>
        /// <returns></returns>
        public int AccomplishData(String orderNos)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = " UPDATE dbo.mes_work_order SET order_status=50 WHERE order_status>20 AND  order_no in (" + orderNos + ") ;";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }


        //事务批量执行添加、修改
        public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }





        #endregion




        #region  成员方法


        /*
        /// <summary>
        /// 根据传入条件获得菜单列表数据
        /// </summary>
        public List<SysPartCustOrder> getList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                strWhere += " and enabled='Y' ";
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select  * from dbo.sys_part_cust_order ");
                if (strWhere != null && !strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                return dbConn.Query<SysPartCustOrder>(strSql.ToString()).ToList();
            }
        }

       
        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveAdjustOrder(SysPartCustOrder Params)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_part_cust_order (part_id ");
                SqlStringBuilder.Append("						, cust_order ");
                SqlStringBuilder.Append("						, enabled ");
                SqlStringBuilder.Append("						, create_userid ");
                SqlStringBuilder.Append("						, create_time ");
                SqlStringBuilder.Append("						,ship_unit) ");
                SqlStringBuilder.Append("					VALUES(@partId ");
                SqlStringBuilder.Append("						, @custOrder ");
                SqlStringBuilder.Append("						, 'Y' ");
                SqlStringBuilder.Append("						, @createUserid ");
                SqlStringBuilder.Append("						, getdate()");
                SqlStringBuilder.Append("						,@shipUnit)");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result;
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateAdjustOrder(SysPartCustOrder Params)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update sys_part_cust_order  ");
                SqlStringBuilder.Append("SET cust_order=@custOrder ");
                SqlStringBuilder.Append("	, ship_unit=@shipUnit ");
                SqlStringBuilder.Append("	,update_userid = @updateUserId ");
                SqlStringBuilder.Append("	,update_time = getdate()");
                SqlStringBuilder.Append("WHERE part_id=@partId	 ");
                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result;
            }
        }

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteAdjustOrder(String part_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable Params = new Hashtable();
                Params.Add("@part_id", part_id);
                String delStr = "update sys_part_cust_order set Enabled='N'  where part_id = @part_id";
                int result = dbConn.Execute(delStr, Params);
                return result;
            }
        }

        /// <summary>
        /// 上移
        /// </summary>
        public bool upRow(int seq, int ruid, int up_ruid, int up_seq)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> SqlStrings = new List<string>(3);
                List<Hashtable> Parameters = new List<Hashtable>(3);

                Hashtable Params = new Hashtable(4);

                Params.Add("@seq", seq);
                Params.Add("@ruid", ruid);
                Params.Add("@up_ruid", up_ruid);
                Params.Add("@up_seq", up_seq);

                SqlStrings.Add(@" UPDATE dbo.g_workorder_detail SET seq=@up_seq ,update_userid = @updateUserId 
                ,update_time = CONVERT(VARCHAR(10),GETDATE(),23)  WHERE ruid=@ruid");
                Parameters.Add(Params);

                SqlStrings.Add(@"UPDATE dbo.g_workorder_detail SET seq=@seq ,update_userid = @updateUserId ,update_time = CONVERT(VARCHAR(10),GETDATE(),23) 
                   WHERE ruid=@up_ruid");
                Parameters.Add(Params);

                int result = 0;
                for (int i = 0; i < SqlStrings.Count; i++)
                {
                     result = dbConn.Execute(SqlStrings[i], Parameters);

                }
                return result > 0 ? true : false;
            }
        }

        /// <summary>
        /// 上移
        /// </summary>
        public bool upAll(int ruid, int seq, int up_ruid, int up_seq)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> SqlStrings = new List<string>(3);
                List<Hashtable> Parameters = new List<Hashtable>(3);

                Hashtable Params = new Hashtable(4);
                Params.Add("@ruid", ruid);
                Params.Add("@seq", seq);
                Params.Add("@up_ruid", up_ruid);
                Params.Add("@up_seq", up_seq);

                SqlStrings.Add("UPDATE dbo.g_workorder_detail SET seq=@up_seq ,update_userid = @updateUserId ,update_time=getdate() WHERE ruid=@ruid");
                Parameters.Add(Params);

                SqlStrings.Add("UPDATE dbo.g_workorder_detail SET seq=@seq  ,update_userid = @updateUserId ,update_time=getdate()  WHERE ruid=@up_ruid");
                Parameters.Add(Params);

                int result = 0;
                for (int i = 0; i < SqlStrings.Count; i++)
                {
                    result = dbConn.Execute(SqlStrings[i], Parameters);
                }
                return result > 0 ? true : false;
            }
        }

        /// <summary>
        /// 获取上一条数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public List<GWorkorderDetail> selectAll(string CustPDLine)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sb = new StringBuilder();
                sb.Append(" SELECT  ROW_NUMBER() OVER ( ORDER BY a.seq ) AS a ,");
                sb.Append(" a.ruid ,");
                sb.Append(" a.seq ,");
                sb.Append(" a.workorder_id ,");
                sb.Append(" e.part_no AS modelNo ,");
                sb.Append(" b.part_no ,");
                sb.Append(" b.part_spec ,");
                sb.Append(" p.cust_pdline_desc ,");
                sb.Append(" a.type ,");
                sb.Append(" a.create_time ,");
                sb.Append("  g.serial_number ,");
                sb.Append(" w.car_no ,");
                sb.Append(" g.work_flag");
                sb.Append(" FROM    dbo.g_workorder_detail AS a");
                sb.Append(" LEFT JOIN dbo.g_workorder AS d ON a.workorder_id = d.ruid");
                sb.Append(" LEFT JOIN dbo.sys_part AS e ON d.part_id = e.part_id");
                sb.Append(" LEFT JOIN dbo.g_workorder_sn AS f ON a.ruid = f.wo_detail_id");
                sb.Append(" LEFT JOIN dbo.g_sn_status AS g ON f.serial_number = g.serial_number");
                sb.Append(" LEFT JOIN dbo.g_workorder AS w ON w.ruid = a.workorder_id");
                sb.Append(" LEFT JOIN dbo.g_pdline_relation AS r ON r.pdline_id = w.pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_cust_pdline AS p ON p.cust_pdline_id = r.cust_pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_part AS b ON a.part_id = b.part_id   ");
                sb.Append(" WHERE   p.cust_pdline_name='" + CustPDLine + "' and g.work_flag <0  and  a.create_time>(SELECT CONVERT(VARCHAR(10), GETDATE()- 15, 120)) AND a.enabled='Y' ORDER BY  a.seq ");

                List<GWorkorderDetail> result = dbConn.Query<GWorkorderDetail>(sb.ToString()).ToList();

                return result;
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectAdjustOrder()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select part_id as [value] ,part_no as [key] from sys_part where  part_type = '1' and enabled='Y'  ");

                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }


        public int deleteOrder(String guid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (guid == null)
                    return 0;
                string str = "UPDATE dbo.g_workorder_detail SET enabled='N' WHERE ruid IN (" + guid + ")";
                int result = dbConn.Execute(str);
                return result;
            }
        }

        public string getPartLocation(string ruid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT part_location ");
                SqlStringBuilder.Append("FROM  dbo.g_workorder_sn a ");
                SqlStringBuilder.Append(" LEFT JOIN sys_part b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("WHERE a.wo_detail_id = " + ruid + " and a.enabled='Y'  ");

                string result= dbConn.ExecuteScalar(SqlStringBuilder.ToString()).ToString();
                return result;
            }
        }

        public int deleteOrderInfo(String ruid, string custPdlineDesc, string partLocation)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (ruid == null)
                    return 0;

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE dbo.g_workorder_detail ");
                SqlStringBuilder.Append("SET    enabled = 'N' ");
                SqlStringBuilder.Append("WHERE  ruid IN ( ");
                SqlStringBuilder.Append("       SELECT  a.ruid ");
                SqlStringBuilder.Append("       FROM    dbo.g_workorder_detail a ");
                SqlStringBuilder.Append("               LEFT JOIN dbo.g_workorder b ON a.workorder_id = b.ruid ");
                SqlStringBuilder.Append("               LEFT JOIN dbo.g_workorder_sn c ON a.ruid = c.wo_detail_id ");
                SqlStringBuilder.Append("               LEFT JOIN dbo.sys_pdline d ON b.pdline_id = d.pdline_id ");
                SqlStringBuilder.Append("               LEFT JOIN sys_part e ON c.part_id = e.part_id ");
                SqlStringBuilder.Append("               LEFT JOIN dbo.g_pdline_relation f ON d.pdline_id = f.pdline_id ");
                SqlStringBuilder.Append("               LEFT JOIN dbo.sys_cust_pdline g ON f.cust_pdline_id = g.cust_pdline_id ");
                SqlStringBuilder.Append("       WHERE   a.enabled = 'Y' ");
                SqlStringBuilder.Append("               AND g.cust_pdline_name = @custPdlineDesc ");
                SqlStringBuilder.Append("                AND a.ruid >= @ruid ");
                if (partLocation != "01" && partLocation != "02")
                {

                    SqlStringBuilder.Append("                AND e.part_location NOT IN ( '01', '02' ) ");
                }
                else
                {
                    SqlStringBuilder.Append("                AND e.part_location IN ( '01', '02' ) ");
                }
                SqlStringBuilder.Append("                 ) ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@ruid", ruid);
                parameters.Add("@custPdlineDesc", custPdlineDesc);

                int result = dbConn.Execute(SqlStringBuilder.ToString(), parameters);
                return result;
            }
        }

        //启用
        public int EnableData(String id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_workorder_detail set Enabled='Y'  WHERE ruid = @ruid";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@ruid", id);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        //禁用
        public int DisableData(String id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update g_workorder_detail set Enabled='N'  WHERE ruid = @ruid";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@ruid", id);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string OrderBy, string stageId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);

                StringBuilder sb = new StringBuilder();
                sb.Append(" dbo.g_workorder_detail AS a");
                sb.Append(" LEFT JOIN dbo.g_workorder AS d ON a.workorder_id=d.ruid LEFT JOIN dbo.sys_part AS e ON d.part_id=e.part_id");
                sb.Append(" LEFT JOIN dbo.g_workorder_sn AS f ON a.ruid=f.wo_detail_id");
                sb.Append(" LEFT JOIN dbo.g_sn_status AS g ON f.serial_number=g.serial_number");
                sb.Append(" LEFT JOIN dbo.g_workorder AS w ON w.ruid = a.workorder_id");
                sb.Append(" LEFT JOIN dbo.g_pdline_relation AS r ON r.pdline_id = w.pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_cust_pdline AS p ON p.cust_pdline_id = r.cust_pdline_id");
                sb.Append(" LEFT JOIN dbo.sys_part AS b ON a.part_id = b.part_id   ");
                sb.Append(" LEFT JOIN dbo.g_workorder_seq AS c ON a.ruid = c.workorder_id   ");
                sb.Append(" LEFT JOIN dbo.sys_enum AS i ON i.enum_value = b.part_location   ");
                sb.Append(" AND i.enum_type = 'sys_part_location' AND i.enabled = 'Y'   ");
                sb.Append(" LEFT JOIN dbo.g_stage_location AS h ON h.enum_id = i.enum_id   ");

                parameters.Add("@Table", sb.ToString());

                StringBuilder sbColumn = new StringBuilder();
                sbColumn.Append(" ROW_NUMBER() OVER ( ORDER BY a.seq ) AS a ,");
                sbColumn.Append(" a.ruid ,");
                sbColumn.Append(" c.seq ,");
                sbColumn.Append(" a.workorder_id ,");
                sbColumn.Append(" e.part_no AS modelNo,");
                sbColumn.Append(" b.part_no ,");
                sbColumn.Append(" b.part_spec ,");
                sbColumn.Append(" p.cust_pdline_desc ,");
                sbColumn.Append(" a.type ,");
                sbColumn.Append(" a.create_time ,");
                sbColumn.Append(" g.serial_number ,");
                sbColumn.Append(" w.car_no,");
                sbColumn.Append(" b.cust_part_no");
                String strWhere = " c.stage_id = " + stageId;
                strWhere += " AND h.stage_id = " + stageId;
                strWhere += " AND  a.create_time>(SELECT CONVERT(VARCHAR(10), GETDATE()- 15, 120))";
                strWhere += " AND a.enabled='Y'";
                strWhere += " AND c.seq > ( SELECT    MAX(wo_detail_seq)";
                strWhere += " FROM      dbo.sys_terminal AS a";
                strWhere += " LEFT JOIN dbo.sys_terminal_point AS b ON a.terminal_id = b.terminal_id";
                strWhere += " WHERE     stage_id = " + stageId + ")";

                parameters.Add("@Column", sbColumn.ToString());
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                List<AdjustOrder> dataList = dbConn.Query<AdjustOrder>(sb.ToString(), parameters).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }
            */
        #endregion  成员方法
    }


}

    
